﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Xoohoo.Extensions;
using Xoohoo.Models;
using Xoohoo.Models.InputModels;
using System.Transactions;

namespace Xoohoo.Repositories.SqlServerEntLibDAB
{
    public class UserGroupRepository : RepositoryBase,IUserGroupRepository
    {
        private readonly Database _db;

        public UserGroupRepository(string connectionStringName)
        {
            this._db = DatabaseFactory.CreateDatabase(connectionStringName);

        }

        #region IUserGroupRepository Members

        public UserGroup GetItem(Guid userGroupID)
        {
            string sql = "select * from UserGroup Where UserGroupID=@UserGroupID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID", DbType.String, userGroupID.ToString());

            UserGroup item = GetItem(cmd);

            if (item != null)
                item.Roles = ProjectUserGroupRoles(item.UserGroupID);

            return item;
        }

        public UserGroup GetItem(string userGroupName)
        {
            string sql = "select TOP 1 * from UserGroup Where UserGroupName=@UserGroupName";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupName", DbType.String, userGroupName);

            UserGroup item = GetItem(cmd);

            if (item != null)
                item.Roles = ProjectUserGroupRoles(item.UserGroupID);

            return item;
        }

        public List<UserGroup> GetList(Guid? parentID = null)
        {
            string sql;
            if (parentID.HasValue)
            {
                var parent = GetItem(parentID.Value);
                if (parent == null) return null;

                int? displayOrder = GetDisplayOrderOfNextParentOrNextBrother(parent.DisplayOrder, parent.UserGroupLevel);
                if (displayOrder.HasValue)
                    sql = "select * from UserGroup Where DisplayOrder>={0} And DisplayOrder<{1} Order by DisplayOrder"
                        .FormatWith(parent.DisplayOrder, displayOrder);
                else
                    sql = "select * from UserGroup Where DisplayOrder>={0} Order by DisplayOrder"
                        .FormatWith(parent.DisplayOrder, displayOrder);
            }
            else
            {
                sql = "select * from UserGroup Order by DisplayOrder";
            }

            DbCommand cmd = _db.GetSqlStringCommand(sql);
            IDataReader dr = _db.ExecuteReader(cmd);

            var list = ProjectList(dr);

            dr.Close();

            return list;

        }

        public List<UserGroup> GetParentList(Guid userGroupID)
        {
            string sql = @"WITH CET AS
                     (
                     SELECT UserGroupID,UserGroupName,UserGroupLevel,DisplayOrder,ParentID,IsSystem
                     FROM UserGroup
                     WHERE  UserGroupID = @UserGroupID
                     UNION ALL
                     SELECT P.UserGroupID,P.UserGroupName,P.UserGroupLevel,P.DisplayOrder,P.ParentID,P.IsSystem
                     FROM UserGroup P
                     JOIN CET Curr ON Curr.ParentID = P.UserGroupID
                     )
                     SELECT UserGroupID,UserGroupName,UserGroupLevel,DisplayOrder,ParentID,IsSystem 
                     FROM CET ORDER BY DisplayOrder";

            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
            IDataReader dr = _db.ExecuteReader(cmd);
            var list = ProjectList(dr);
            dr.Close();
            return list;
        }

        public List<UserGroupBasic> GetParentBasicList(Guid userGroupID)
        {
            string sql = @"WITH CET AS
                     (
                     SELECT UserGroupID,UserGroupName,DisplayOrder,ParentID,IsSystem
                     FROM UserGroup
                     WHERE  UserGroupID = @UserGroupID
                     UNION ALL
                     SELECT P.UserGroupID,P.UserGroupName,P.DisplayOrder,P.ParentID,P.IsSystem
                     FROM UserGroup P
                     JOIN CET Curr ON Curr.ParentID = P.UserGroupID
                    )
                    SELECT UserGroupID,UserGroupName,DisplayOrder,ParentID,IsSystem 
                    FROM CET ORDER BY DisplayOrder";

            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
            IDataReader dr = _db.ExecuteReader(cmd);
            var list = ProjectBasicList(dr);
            dr.Close();
            return list;
        }

        public bool Save(UserGroupInput userGroupInput)
        {
            string sql;
            DbCommand cmd;

            //要保存的对象
            UserGroup userGroupToSave = null;

            if (!userGroupInput.UserGroupID.IsNullOrEmpty())
            {
                userGroupToSave = GetItem(userGroupInput.UserGroupID.Value);

                //尝试编辑一条不存在的记录
                if (userGroupInput == null) return false;
            }

            //添加操作
            if (userGroupToSave == null)
            {
                #region 添加操作

                //创建要保存的对象
                userGroupToSave = new UserGroup
                {
                    UserGroupID = Guid.NewGuid(),
                    ParentID = userGroupInput.ParentID,
                    UserGroupName = userGroupInput.UserGroupName,
                };

                //如果添加的是新的顶级节点,直接添加到末尾，不会影响其他节点
                if (userGroupInput.ParentID == Guid.Empty)
                {
                    userGroupToSave.DisplayOrder = GetMaxDisplayOrder() + 1;
                    userGroupToSave.UserGroupLevel = 1;
                }
                else//如果添加的是子节点，会影响其他节点的DisplayOrder
                {
                    //父节点树的最大DisplayerOrder
                    int maxDisplayOrderInParentTree = GetMaxDisplayOrderInParentTree(userGroupInput.ParentID.Value);
                    //父节点树的最大DisplayerOrder基础上加1作为保存对象的DisplayOrder
                    userGroupToSave.DisplayOrder = maxDisplayOrderInParentTree + 1;
                    //父节点的Level基础上加1作为保存对象的Level
                    userGroupToSave.UserGroupLevel = GetLevel(userGroupInput.ParentID.Value) + 1;

                    //父节点树之后的所有节点的DisplayOrder加1
                    sql = "Update UserGroup Set DisplayOrder=DisplayOrder+1 Where DisplayOrder > @DisplayOrder";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, maxDisplayOrderInParentTree);
                    _db.ExecuteNonQuery(cmd);
                }

                //将对象保存进数据库
                sql = "Insert Into UserGroup(UserGroupID,ParentID,UserGroupName,DisplayOrder,UserGroupLevel,IsSystem)"
                    + "Values(@UserGroupID,@ParentID,@UserGroupName,@DisplayOrder,@UserGroupLevel,@IsSystem)";

                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupToSave.UserGroupID);
                _db.AddInParameter(cmd, "ParentID", DbType.Guid, userGroupToSave.ParentID);
                _db.AddInParameter(cmd, "UserGroupName", DbType.String, userGroupToSave.UserGroupName);
                _db.AddInParameter(cmd, "DisplayOrder", DbType.String, userGroupToSave.DisplayOrder);
                _db.AddInParameter(cmd, "UserGroupLevel", DbType.Int32, userGroupToSave.UserGroupLevel);
                _db.AddInParameter(cmd, "IsSystem", DbType.Boolean, 0);
                _db.ExecuteNonQuery(cmd);

                #endregion
            }
            else//编辑操作
            {
                #region 编辑操作

                userGroupToSave.UserGroupName = userGroupInput.UserGroupName;

                //如果父节点不改变，则仅仅保存数据就行了。下面处理的是父节点改变了的情况
                //如果父节点改变(从无父节点到有父节点，从有父节点到无父节点，从一个父节点到另一个父节点)
                if (userGroupInput.ParentID != userGroupToSave.ParentID)
                {
                    userGroupToSave.ParentID = userGroupInput.ParentID;

                    //获取当前节点的下一个兄弟节点或更高层下一个父节点（不是自己的父节点）的DisplayOrder
                    int? displayOrderOfNextParentOrNextBrother = GetDisplayOrderOfNextParentOrNextBrother(userGroupToSave.DisplayOrder, userGroupToSave.UserGroupLevel);

                    #region 当前节点树ID集合

                    int currentTreeItemCount;
                    List<Guid> currTreeIDs = new List<Guid>();
                    if (!displayOrderOfNextParentOrNextBrother.HasValue)
                    {
                        //说明当前节点是最后一个节点,直接获取
                        sql = "Select UserGroupID From UserGroup Where  DisplayOrder>=@DisplayOrder";
                        cmd = _db.GetSqlStringCommand(sql);
                        _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToSave.DisplayOrder);
                    }
                    else
                    {
                        sql = "Select UserGroupID From UserGroup Where DisplayOrder>=@DisplayOrder And DisplayOrder<@DOONPONB";
                        cmd = _db.GetSqlStringCommand(sql);
                        _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToSave.DisplayOrder);
                        _db.AddInParameter(cmd, "DOONPONB", DbType.Int32, displayOrderOfNextParentOrNextBrother);
                    }
                    var dr = _db.ExecuteReader(cmd);

                    while (dr.Read())
                    {
                        currTreeIDs.Add(dr.GetGuid(0));
                    }
                    dr.Close();
                    currentTreeItemCount = currTreeIDs.Count;

                    #endregion

                    //当前节点将由子节点升为顶级节点，直接将该节点数移到最后
                    if (!userGroupToSave.ParentID.HasValue)
                    {
                        #region 将由子节点升为顶级节点

                        //需要提升的层级数
                        int xLevel = userGroupToSave.UserGroupLevel - 1;

                        //当前节点树之后已无任何节点
                        if (!displayOrderOfNextParentOrNextBrother.HasValue)
                        {
                            //将当前节点树的所有节点的Level都进行提升
                            sql = "Update UserGroup Set UserGroupLevel = UserGroupLevel - @Level Where DisplayOrder>=@DisplayOrder";
                            cmd = _db.GetSqlStringCommand(sql);
                            _db.AddInParameter(cmd, "Level", DbType.Int32, xLevel);
                            _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToSave.DisplayOrder);
                            _db.ExecuteNonQuery(cmd);
                        }
                        else//当前节点树之后还有节点，应该将这些节点的向前面排，并且将当前节点树的所有节点往后排
                        {
                            //当前节点树之后的节点数量
                            sql = "Select Count(1) From UserGroup Where DisplayOrder>=@DOONPONB";
                            cmd = _db.GetSqlStringCommand(sql);
                            _db.AddInParameter(cmd, "DOONPONB", DbType.Int32, displayOrderOfNextParentOrNextBrother);
                            int nextItemCount = _db.ExecuteScalarInt(cmd).Value;

                            sql = "Update UserGroup Set DisplayOrder = DisplayOrder - @CTIC Where DisplayOrder>=@DOONPONB";
                            cmd = _db.GetSqlStringCommand(sql);
                            _db.AddInParameter(cmd, "CTIC", DbType.Int32, currentTreeItemCount);
                            _db.AddInParameter(cmd, "DOONPONB", DbType.Int32, displayOrderOfNextParentOrNextBrother);
                            _db.ExecuteNonQuery(cmd);

                            sql = "Update UserGroup Set UserGroupLevel = UserGroupLevel - @Level,DisplayOrder = DisplayOrder + @NextNoteCount Where 1<>1 ";
                            foreach (var id in currTreeIDs)
                                sql += " Or UserGroupID = '{0}'".FormatWith(id.ToString());
                            cmd = _db.GetSqlStringCommand(sql);
                            _db.AddInParameter(cmd, "Level", DbType.Int32, xLevel);
                            _db.AddInParameter(cmd, "NextItemCount", DbType.Int32, nextItemCount);
                            _db.ExecuteNonQuery(cmd);

                        }

                        #endregion
                    }
                    else//当前节点将改变父节点，包括从顶级节点移至另一节点下，或从当前父节点下移至另一节点下
                    {
                        #region 从顶级节点移至另一节点下，或从当前父节点下移至另一节点下

                        //目标父节点
                        var tarParent = GetItem(userGroupInput.ParentID.Value);

                        int xDisplayOrder = userGroupToSave.DisplayOrder - tarParent.DisplayOrder;
                        int xLevel = userGroupToSave.UserGroupLevel - tarParent.UserGroupLevel;

                        if (xDisplayOrder > 0)//从下往上移
                        {
                            #region 从下往上移
                            //特例处理，当前节点要移至的父节点就是上一个节点，只需要改变当前树Level
                            if (xDisplayOrder == 1)
                            {
                                sql = "Update UserGroup Set UserGroupLevel = UserGroupLevel - @Level Where 1<>1 ";
                                foreach (var id in currTreeIDs)
                                    sql += " Or UserGroupID = '{0}'".FormatWith(id.ToString());
                                cmd = _db.GetSqlStringCommand(sql);
                                _db.AddInParameter(cmd, "Level", DbType.Int32, xLevel - 1);
                                _db.ExecuteNonQuery(cmd);

                            }
                            else
                            {
                                //新的父节点和本节点之间的节点往下移动，DisplayOrder增加
                                sql = "Update UserGroup Set DisplayOrder=DisplayOrder+@CurTreeCount Where DisplayOrder>@TDisplayOrder And DisplayOrder<@CDisplayOrder";
                                cmd = _db.GetSqlStringCommand(sql);
                                _db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, currentTreeItemCount);
                                _db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, tarParent.DisplayOrder);
                                _db.AddInParameter(cmd, "CDisplayOrder", DbType.Int32, userGroupToSave.DisplayOrder);
                                _db.ExecuteNonQuery(cmd);

                                sql = "Update UserGroup Set DisplayOrder = DisplayOrder-@XCount,UserGroupLevel = UserGroupLevel - @Level Where 1<>1 ";
                                foreach (var id in currTreeIDs)
                                    sql += " Or UserGroupID = '{0}'".FormatWith(id.ToString());
                                cmd = _db.GetSqlStringCommand(sql);
                                _db.AddInParameter(cmd, "XCount", DbType.Int32, xDisplayOrder - 1);//也就是新节点和本节点之间的节点的数量
                                _db.AddInParameter(cmd, "Level", DbType.Int32, xLevel - 1);
                                _db.ExecuteNonQuery(cmd);
                            }
                            #endregion
                        }
                        else//从上往下移
                        {
                            #region 从上往下移
                            // 本节点树下已经不存在任何节点了，当然无法向下移
                            if (!displayOrderOfNextParentOrNextBrother.HasValue)
                                return false;

                            // 更新本节点树至新的父节点（包括新的父节点）之间的节点的DisplayOrder
                            sql = "Update UserGroup Set DisplayOrder=DisplayOrder-@CurTreeCount Where DisplayOrder>=@DOONPONB And DisplayOrder<=@TDisplayOrder";
                            cmd = _db.GetSqlStringCommand(sql);
                            _db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, currentTreeItemCount);
                            _db.AddInParameter(cmd, "DOONPONB", DbType.Int32, displayOrderOfNextParentOrNextBrother);
                            _db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, tarParent.DisplayOrder);
                            _db.ExecuteNonQuery(cmd);

                            // 本节点至新的节点之间的节点数
                            int nextItemCount = tarParent.DisplayOrder - displayOrderOfNextParentOrNextBrother.Value + 1;
                            sql = "Update UserGroup Set DisplayOrder = DisplayOrder+@XCount,UserGroupLevel = UserGroupLevel - @Level Where 1<>1 ";
                            foreach (var id in currTreeIDs)
                                sql += " Or UserGroupID = '{0}'".FormatWith(id.ToString());
                            cmd = _db.GetSqlStringCommand(sql);
                            _db.AddInParameter(cmd, "XCount", DbType.Int32, nextItemCount);
                            _db.AddInParameter(cmd, "Level", DbType.Int32, xLevel - 1);
                            _db.ExecuteNonQuery(cmd);

                            #endregion
                        }

                        #endregion
                    }
                }

                //清理工作
                sql = "Delete UserGroupRoleRelationShip Where UserGroupID=@UserGroupID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserGroupID", DbType.String, userGroupToSave.UserGroupID.ToString());
                _db.ExecuteNonQuery(cmd);

                //当前节点的DisplayOrder和Level已经改变，所以只需要更新其他字段
                sql = "Update UserGroup Set ParentID=@ParentID,UserGroupName=@UserGroupName Where UserGroupID=@UserGroupID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "ParentID", DbType.Guid, userGroupToSave.ParentID);
                _db.AddInParameter(cmd, "UserGroupName", DbType.String, userGroupToSave.UserGroupName);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupToSave.UserGroupID);
                _db.ExecuteNonQuery(cmd);

                #endregion
            }

            //关系处理
            if (userGroupInput.Roles != null)
            {
                sql = "Insert Into UserGroupRoleRelationShip(UserGroupID,RoleID) Values "
                    + "(@UserGroupID,@RoleID)";
                cmd = _db.GetSqlStringCommand(sql);

                foreach (var roleID in userGroupInput.Roles)
                {
                    cmd.Parameters.Clear();
                    _db.AddInParameter(cmd, "UserGroupID", DbType.String, userGroupInput.UserGroupID.ToString());
                    _db.AddInParameter(cmd, "RoleID", DbType.String, roleID);
                    _db.ExecuteNonQuery(cmd);
                }
            }

            return true;
        }

        public bool Remove(Guid userGroupID)
        {
            using (TransactionScope ts = new TransactionScope())
            {

                //移除无限级分类步骤：
                //1、获取预删节点信息
                UserGroup userGroupToRemove = GetItem(userGroupID);
                //当然，如果无法获取节点，属于无效操作；另外，超级管理员组和等待分配组不允许被删除
                if (userGroupToRemove == null || userGroupToRemove.IsSystem) return false;

                //2、节点包含子节点不允许删除
                string sql = "SELECT CASE WHEN EXISTS (SELECT NULL FROM {0}) THEN 1 ELSE 0 END AS Result".FormatWith("UserGroup where ParentID=@UserGroupID");
                DbCommand cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
                if (Convert.ToBoolean(_db.ExecuteScalar(cmd)))
                    return false;

                //3、更新用户表
                sql = "Select UserGroupID From UserGroup Where DisplayOrder = 2";
                cmd = _db.GetSqlStringCommand(sql);
                object o = _db.ExecuteScalar(cmd);
                if (o == null) return false;

                sql = "Update [User] Set UserGroupID=@TUserGroupID Where UserGroupID=@UserGroupID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
                _db.AddInParameter(cmd, "TUserGroupID", DbType.Guid, o);
                _db.ExecuteNonQuery(cmd);

                //4、更新DisplayOrder大于预删节点DisplayOrder的节点
                sql = "Update UserGroup Set DisplayOrder=DisplayOrder-1 Where DisplayOrder>@DisplayOrder";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToRemove.DisplayOrder);
                _db.ExecuteNonQuery(cmd);

                //5、删除关联节点
                sql = "Delete UserGroupRoleRelationShip Where UserGroupID=@UserGroupID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
                _db.ExecuteNonQuery(cmd);

                //6、删除节点
                sql = "Delete UserGroup Where UserGroupID=@UserGroupID";
                cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, userGroupID);
                _db.ExecuteNonQuery(cmd);

                ts.Complete();

                return true;
            }
        }

        public bool Move(Guid userGroupID, MovingTarget target)
        {
            using (TransactionScope ts = new TransactionScope())
            {

                string sql;
                DbCommand cmd;

                var userGroupToMove = GetItem(userGroupID);
                // 保证DisplayOrder为 1 的“系统管理组”和“等待分配组”不被移动
                if (userGroupToMove == null || userGroupToMove.DisplayOrder <= 2) return false;

                #region 获取当前节点树
                int curTreeCount;
                List<Guid> currTreeIDs = new List<Guid>();

                int? displayOrderOfNextParentOrNextBrother = GetDisplayOrderOfNextParentOrNextBrother(userGroupToMove.DisplayOrder, userGroupToMove.UserGroupLevel);

                if (!displayOrderOfNextParentOrNextBrother.HasValue)
                {
                    sql = "Select UserGroupID From UserGroup Where  DisplayOrder>=@DisplayOrder";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToMove.DisplayOrder);

                }
                else
                {
                    sql = "Select UserGroupID From UserGroup Where DisplayOrder>=@DisplayOrder And DisplayOrder<@DOONPONB";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToMove.DisplayOrder);
                    _db.AddInParameter(cmd, "DOONPONB", DbType.Int32, displayOrderOfNextParentOrNextBrother);
                }
                var dr = _db.ExecuteReader(cmd);

                while (dr.Read())
                {
                    currTreeIDs.Add(dr.GetGuid(0));
                }
                dr.Close();

                curTreeCount = currTreeIDs.Count;

                #endregion

                // 防止DisplayOrder为3的项非法篡改"等待分配组",也就是说DisplayOrder必须大于或等于4
                if (MovingTarget.Up == target)
                {
                    if (userGroupToMove.DisplayOrder < 4) return false;

                    #region 获取上一个兄弟节点
                    sql = "select top 1 * from UserGroup Where ParentID=@ParentID And DisplayOrder<@DisplayOrder Order by DisplayOrder desc";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "ParentID", DbType.Guid, userGroupToMove.ParentID);
                    _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToMove.DisplayOrder);
                    dr = _db.ExecuteReader(cmd);

                    UserGroup targetUserGroup = ProjectBeforeRead(dr);

                    dr.Close();
                    #endregion

                    if (targetUserGroup == null) return false;

                    //获取兄弟节点树的节点数
                    sql = "Select Count(1) From UserGroup Where DisplayOrder>=@TDisplayOrder And DisplayOrder<@CDisplayOrder";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, curTreeCount);
                    _db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, targetUserGroup.DisplayOrder);
                    _db.AddInParameter(cmd, "CDisplayOrder", DbType.Int32, userGroupToMove.DisplayOrder);
                    int targetTreeCount = _db.ExecuteScalarInt(cmd).Value;

                    //更新兄弟节点树的DisplayOrder
                    sql = "Update UserGroup Set DisplayOrder = DisplayOrder+@CurTreeCount Where DisplayOrder>=@TDisplayOrder And DisplayOrder<@CDisplayOrder";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, curTreeCount);
                    _db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, targetUserGroup.DisplayOrder);
                    _db.AddInParameter(cmd, "CDisplayOrder", DbType.Int32, userGroupToMove.DisplayOrder);
                    _db.ExecuteNonQuery(cmd);

                    sql = "Update UserGroup Set DisplayOrder = DisplayOrder-@TargetTreeCount Where 1<>1 ";
                    foreach (var id in currTreeIDs)
                        sql += " Or UserGroupID = '{0}'".FormatWith(id.ToString());
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "TargetTreeCount", DbType.Int32, targetTreeCount);
                    _db.ExecuteNonQuery(cmd);

                }
                else
                {
                    #region 获取下一个兄弟节点
                    sql = "select top 1 * from UserGroup Where ParentID=@ParentID And DisplayOrder>@DisplayOrder Order by DisplayOrder";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "ParentID", DbType.Guid, userGroupToMove.ParentID);
                    _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, userGroupToMove.DisplayOrder);

                    dr = _db.ExecuteReader(cmd);
                    UserGroup targetUserGroup = ProjectBeforeRead(dr);

                    dr.Close();
                    #endregion

                    if (targetUserGroup == null) return false;

                    #region 获取兄弟节点树的节点数
                    int? displayOrderOfNextParentOrNextBrotherOfTarget = GetDisplayOrderOfNextParentOrNextBrother(targetUserGroup.DisplayOrder, targetUserGroup.UserGroupLevel);

                    int targetTreeCount;
                    if (!displayOrderOfNextParentOrNextBrotherOfTarget.HasValue)
                    {
                        sql = "Select Count(1) From UserGroup Where DisplayOrder>=@DisplayOrder";
                        cmd = _db.GetSqlStringCommand(sql);
                        _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, targetUserGroup.DisplayOrder);
                        targetTreeCount = _db.ExecuteScalarInt(cmd).Value;
                    }
                    else
                    {
                        sql = "Select Count(1) From UserGroup Where DisplayOrder>=@DisplayOrder And DisplayOrder<@TDisplayOrder";
                        cmd = _db.GetSqlStringCommand(sql);
                        _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, targetUserGroup.DisplayOrder);
                        _db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, displayOrderOfNextParentOrNextBrotherOfTarget);
                        targetTreeCount = _db.ExecuteScalarInt(cmd).Value;
                    }
                    #endregion

                    //更新兄弟节点树的DisplayOrder
                    sql = "Update UserGroup Set DisplayOrder = DisplayOrder-@CurTreeCount Where DisplayOrder>=@DisplayOrder And DisplayOrder<@TDisplayOrder";
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "CurTreeCount", DbType.Int32, curTreeCount);
                    _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, targetUserGroup.DisplayOrder);
                    _db.AddInParameter(cmd, "TDisplayOrder", DbType.Int32, targetUserGroup.DisplayOrder + targetTreeCount);
                    _db.ExecuteNonQuery(cmd);


                    sql = "Update UserGroup Set DisplayOrder = DisplayOrder+@TargetTreeCount Where 1<>1 ";
                    foreach (var id in currTreeIDs)
                        sql += " Or UserGroupID = '{0}'".FormatWith(id.ToString());
                    cmd = _db.GetSqlStringCommand(sql);
                    _db.AddInParameter(cmd, "TargetTreeCount", DbType.Int32, targetTreeCount);
                    _db.ExecuteNonQuery(cmd);

                }

                ts.Complete();
                return true;
            }
        }

        #endregion

        #region Private Methods

        private UserGroup GetItem(DbCommand cmd)
        {
            IDataReader dr = _db.ExecuteReader(cmd);
            UserGroup item = ProjectBeforeRead(dr);
            dr.Close();
            return item;
        }

        private List<UserGroup> ProjectList(IDataReader dr)
        {
            if (dr == null) return new List<UserGroup>();

            var list = new List<UserGroup>();

            while (dr.Read())
            {
                list.Add(Project(dr));
            }
            return list;
        }

        private List<UserGroupBasic> ProjectBasicList(IDataReader dr)
        {
            if (dr == null) return new List<UserGroupBasic>();

            var list = new List<UserGroupBasic>();

            while (dr.Read())
            {
                list.Add(dr.ToModel<UserGroupBasic>());
            }
            return list;
        }

        private UserGroup Project(IDataReader dr)
        {
            if (dr == null) return null;

            return dr.ToModel<UserGroup>();
        }

        private UserGroup ProjectBeforeRead(IDataReader dr)
        {
            if (dr == null || !dr.Read()) return null;

            return Project(dr);
        }

        private List<RoleBasic> ProjectUserGroupRoles(Guid userGroupID)
        {
            List<RoleBasic> list = new List<RoleBasic>();

            string sql = "Select r.RoleID,r.RoleName From Role r Inner Join UserGroupRoleRelationShip ugr on r.RoleID=ugr.RoleID Where ugr.UserGroupID=@UserGroupID";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID", DbType.String, userGroupID.ToString());
            IDataReader dr = _db.ExecuteReader(cmd);

            if (dr == null) return list;

            while (dr.Read())
            {
                list.Add(dr.ToModel<RoleBasic>());
            }
            dr.Close();

            return list;
        }

        private int GetMaxDisplayOrder()
        {
            string sql = "Select Max(DisplayOrder) From UserGroup";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            return _db.ExecuteScalarInt(cmd) ?? 0;
        }

        private int GetMaxDisplayOrderInParentTree(Guid parentID)
        {
            int maxDisplayOrder;
            var parent = GetItem(parentID);
            if (parent == null)
                throw new Exception("或许尝试将节点加到不存在的父节点之上");

            //获取父节点之下的兄弟节点或更高层次的父节点(不是自己的父节点)的DisplayOrder
            int? displayOrderOfNextParentOrNextBrother = GetDisplayOrderOfNextParentOrNextBrother(parent.DisplayOrder, parent.UserGroupLevel);

            if (!displayOrderOfNextParentOrNextBrother.HasValue)
            {
                string sql = "select MAX(DisplayOrder) from UserGroup Where DisplayOrder>@DisplayOrder Or UserGroupID=@UserGroupID";
                DbCommand cmd = _db.GetSqlStringCommand(sql);
                _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, parent.DisplayOrder);
                _db.AddInParameter(cmd, "UserGroupID", DbType.Guid, parentID);
                maxDisplayOrder = _db.ExecuteScalarInt(cmd).Value;
            }
            else
                maxDisplayOrder = displayOrderOfNextParentOrNextBrother.Value - 1;

            return maxDisplayOrder;

        }

        //获取下一个
        private int? GetDisplayOrderOfNextParentOrNextBrother(int displayOrder, int userGroupLevel)
        {
            string sql = "select DisplayOrder from UserGroup Where UserGroupLevel<=@UserGroupLevel And DisplayOrder>@DisplayOrder Order by DisplayOrder";
            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupLevel", DbType.Int32, userGroupLevel);
            _db.AddInParameter(cmd, "DisplayOrder", DbType.Int32, displayOrder);
            return _db.ExecuteScalarInt(cmd);
        }

        private int GetLevel(Guid pessionID)
        {
            string sql = "select UserGroupLevel from UserGroup Where UserGroupID=@UserGroupID";

            DbCommand cmd = _db.GetSqlStringCommand(sql);
            _db.AddInParameter(cmd, "UserGroupID", DbType.String, pessionID.ToString());

            return _db.ExecuteScalarInt(cmd).Value;
        }

        #endregion

    }
}
